# -*- coding: utf-8 -*-
import pymysql
from db import config


class Model():
    def __init__(self, table, cinfig=config):
        '''构造方法，初始化表名，连接数据库'''

        '''单表信息操作类'''
        self.tab_name = table  # 表名
        self.link = None  # 数据库连接对象
        self.cursor = None  # 游标对象
        self.fields = []  # 当前的字段名列表
        self.PK = 'id'  # 表的主键名

        try:
            self.link = pymysql.connect(host=config.HOST, user=config.USER, password=config.PASSWD, db=config.DBNAME,
                                        port=config.PORT, charset="utf8")
            self.cursor = self.link.cursor(pymysql.cursors.DictCursor)
            self.__loadFields()

        except Exception as err:
            print('数据库操作对象初始化失败，原因:%s' % err)

    def __loadFields(self):
        '''加载当前表的字段信息，内部私有方法'''
        sql = "SHOW COLUMNS From {0}".format(self.tab_name)
        self.cursor.execute(sql)
        dlist = self.cursor.fetchall()
        for v in dlist:
            # 收集每个字段名称
            self.fields.append(v['Field'])
            # 判断收集主键名称
            if v['Key'] == 'PRI':
                self.PK = v['Field']

    def findAll(self):
        '''获取当前表的所有信息，没有信息返回空列表'''
        try:
            sql = "select * from {0}".format(self.tab_name)
            # print(sql)
            self.cursor.execute(sql)
            return self.cursor.fetchall()
        except Exception as err:
            print('SQL查询执行错误，原因:%s' % err)
            return []

    def find(self, id=0):
        '''获取制定id主键值的单条信息，没有信息返回None'''
        try:
            sql = "select * from {0} where {1}={2}".format(self.tab_name, self.PK, id)
            # print(sql)
            self.cursor.execute(sql)
            return self.cursor.fetchone()
        except Exception as err:
            print('SQL查询执行错误，原因:%s' % err)
            return None

    def select(self, where=[], order=None, limit=None):
        '''获取带条件的信息查询，没有信息返回空列表'''
        try:
            sql = "select * from {0}".format(self.tab_name)
            if isinstance(where, list) and len(where) > 0:
                sql += ' where ' + ' and '.join(where)

            if order is not None:
                sql += " order by " + order
            if limit is not None:
                sql += " limit " + str(limit)

            # print(sql)
            self.cursor.execute(sql)
            return self.cursor.fetchall()

        except Exception as err:
            print('SQL查询执行错误，原因:%s' % err)
            return []

    def save(self, data={}):
        '''信息添加'''
        try:
            # sql语句：insert into 表名 （字段列表） values(值列表)
            keys = []
            values = []
            # 获取有效的字段
            for k, v in data.items():
                if k in self.fields:
                    keys.append(str(k))
                    values.append("'" + str(v) + "'")
            # sql = "insert into {0}({1}) values({2})".format(self.tab_name, ','.join(keys),','.join(['%s'] * len(values)))
            sql = "insert into {0}({1}) values({2})".format(self.tab_name, ','.join(keys), ','.join(values))
            # print(sql)
            # self.cursor.execute(sql, tuple(values))
            self.cursor.execute(sql)
            self.link.commit()
            return self.cursor.lastrowid


        except Exception as err:
            print('SQL执行错误，原因:%s' % err)
            return 0

    def update(self, data={}):
        '''信息修改方法'''
        try:
            # sql语句
            values = []
            # 获取有效的字段
            for k, v in data.items():
                if k in self.fields and (k != self.PK):
                    values.append("{0}='{1}'".format(k, v))

            sql = "update {0} set {1} where {2}={3}".format(self.tab_name, ','.join(values), self.PK, data.get(self.PK))
            # print(sql)
            self.cursor.execute(sql)
            self.link.commit()
            return self.cursor.rowcount
            # 返回影响行数


        except Exception as err:
            print('SQL执行错误，原因:%s' % err)
            return 0

    def delete(self, id=0):
        '''信息删除方法'''
        try:
            sql = "delete from {0} where {1}={2}".format(self.tab_name, self.PK, id)
            # print(sql)
            self.cursor.execute(sql)
            self.link.commit()
            return self.cursor.rowcount
            # 返回影响行数

        except Exception as err:
            print('SQL执行错误，原因:%s' % err)
            return 0

    def __del__(self):
        '''关闭游标对象和连接对象'''
        if self.cursor != None:
            self.cursor.close()
        if self.link != None:
            self.link.close()
